<?php
/**
 * Created by PhpStorm.
 * User: locove
 * Date: 15-4-23
 * Time: 下午7:02
 */
class Controller_Stocksale extends Controller_Admin {
    public function before()
    {
        try {
            ACL::Required('stocksale');
        } catch ( HTTP_Exception_403 $e ) {
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect ( URL::base (true) );
        }

        parent::before();
    }

    public function action_index() {
        $this->title = __("实时库存加权销量下载");
        $view        = View::factory('stocksale');
        $this->response->body($view);
    }

    public function action_load () {
        set_time_limit(0);
        ini_set('memory_limit','600M');
        ini_set('max_allowed_packet','33M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $xls= $_FILES['xls']['tmp_name'];
        $objPHPExcel= PHPExcel_IOFactory::load($xls);
        $data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
        $num = count($data);
        $skus_execl=$skus_arr=$error_num=$sku_error_arr=array();

        $sku_str='';
        for($i=2;$i<=$num;$i++){
            $skus_execl[$i-2]= strtoupper(trim($data[$i]['A']));
            $sku_str.=$sku_str==''?"'".strtoupper(trim($data[$i]['A']))."'":",'".strtoupper(trim($data[$i]['A']))."'";
        }
        if($sku_str!=''){
            $skus_arr = Skusystemapi::getSkuIn($sku_str);
            if(!empty($skus_arr)){
                $values = array();

                foreach ($skus_arr as $row)
                {
                    if (isset($row['sku']))
                    {
                        $values[] = strtoupper($row['sku']);
                    }
                }

                $skus_arr =  $values;
                $sku_error_arr = array_diff($skus_execl,$skus_arr);
                if($sku_error_arr)
                {
                    $error_num = array_keys($sku_error_arr);
                }
            }
        }

        $daysales_all = $actual_num_all = $op_print_num_all = $onway_all = array();
        if($sku_str)
        {
            //实际库存信息
            $actual_num_all = Warningtmp::get_inventory($skus_arr);

            //订单占用库存信息
            $op_print_num_all = Warningtmp::get_print_num($skus_arr);


            //在途库存信息
            $onway_all = Warningtmp::get_onway_num($skus_arr);

            //销量信息
            $maxDate = date('Y-m-d', time() - 86400);
            $daysales_all_string = "SELECT
                `sku`,
                SUM(`daysale7`) `daysales7`,
                SUM(`daysale14`) `daysales14`,
                SUM(`daysale28`) `daysales28`,
                SUM(`daysale`) `daysales`
            FROM
                `sku_sales`
            WHERE
            `count_date` = '{$maxDate}'
            AND
            sku in(".$sku_str.")
            GROUP BY `sku`";

            $return = DB::query(Database::SELECT,$daysales_all_string)->execute('newerp')->as_array();

            if($return)
            {
                foreach ($return as $value)
                {
                    $daysales_all[$value['sku']]['daysales'] = $value['daysales'];
                    $daysales_all[$value['sku']]['daysales7'] = $value['daysales7'];
                    $daysales_all[$value['sku']]['daysales14'] = $value['daysales14'];
                    $daysales_all[$value['sku']]['daysales28'] = $value['daysales28'];
                }
            }
        }
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("YKS")
            ->setLastModifiedBy("YKS")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objActSheet->setCellValue('B1', '在途库存');
        $objActSheet->setCellValue('C1', '加权日均销量');
        $objActSheet->setCellValue('D1', '7天日均销量');
        $objActSheet->setCellValue('E1', '14天日均销量');
        $objActSheet->setCellValue('F1', '28天日均销量');
        $objActSheet->setCellValue('G1', '实际库存数量');
        $objActSheet->setCellValue('H1', '总未发数量');
        $objActSheet->setCellValue('I1', '库存可用数量');
        $objActSheet->setCellValue('J1', '总可用数量');

        foreach($skus_arr as $num => $sku){
            $objActSheet->setCellValue('A'.(($num+2)), $sku);
            $onway_temp=(isset($onway_all[$sku])&&$onway_all[$sku]>0)?$onway_all[$sku]:0;
            $objActSheet->setCellValue('B'.($num+2), $onway_temp);
            $objActSheet->setCellValue('C'.($num+2), isset($daysales_all[$sku]['daysales'])?$daysales_all[$sku]['daysales']:0);
            $objActSheet->setCellValue('D'.($num+2), isset($daysales_all[$sku]['daysales7'])?$daysales_all[$sku]['daysales7']:0);
            $objActSheet->setCellValue('E'.($num+2), isset($daysales_all[$sku]['daysales14'])?$daysales_all[$sku]['daysales14']:0);
            $objActSheet->setCellValue('F'.($num+2), isset($daysales_all[$sku]['daysales28'])?$daysales_all[$sku]['daysales28']:0);

            $actual_temp=isset($actual_num_all[$sku])?$actual_num_all[$sku]:0;
            $op_print_num_temp=isset($op_print_num_all[$sku])?$op_print_num_all[$sku]:0;
            $keyong=$actual_temp-$op_print_num_temp;
            $objActSheet->setCellValue('G'.($num+2), $actual_temp);
            $objActSheet->setCellValue('H'.($num+2), $op_print_num_temp);
            $objActSheet->setCellValue('I'.($num+2), $keyong);
            $objActSheet->setCellValue('J'.($num+2), $keyong+$onway_temp);

        }
        if($sku_error_arr){
            $error_num=0;
            $numbers=count($skus_arr)+2;
            $objActSheet->setCellValue('A'.$numbers, '以下SKU找不到或者找不到数据');
            $error_num++;
            foreach($sku_error_arr as $_sku){
                $objActSheet->setCellValue('A'.($numbers+$error_num), $_sku);
                $error_num++;
            }
        }
        $objPHPExcel->getActiveSheet()->setTitle('在途库存报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'实时库存加权销量'.'.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function action_down_template() {
        $this->title='下载模版';
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("newerp")
            ->setLastModifiedBy("newerp")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objPHPExcel->getActiveSheet()->setTitle('load');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="'.$day.'实时库存加权销量SKU.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
}